from time import sleep

import pandas as pd
from MyTT import *
import akshare as ak
import numpy as np
import os
from datetime import datetime
import pymssql
from sqlalchemy import create_engine, distinct, or_, and_
from urllib.parse import quote_plus as urlquote
from configparser import ConfigParser

pd.set_option('display.max_rows', None)

conf = ConfigParser()
conf.read('env.ini')

sqlserver = ('sqlserver_out', 'sqlserver_in')[0]

host = conf.get(sqlserver, 'host')
port = conf.get(sqlserver, 'port')
user = conf.get(sqlserver, 'user')
password = conf.get(sqlserver, 'password')
schema = conf.get(sqlserver, 'schema')
db = conf.get(sqlserver, 'db')


def get_double_ring():
    sql = "SELECT DISTINCT code FROM STOCK_DAY_DATA"
    r = exec_sql(sql)
    stocks = set([tok[0] for tok in r])
    ret = list()
    N = 55

    for i, code in enumerate(stocks):
        # if i > 100:
        #     break
        print(f'len={len(stocks)}->i={i}->code={code}')
        prefix = code[:2]
        ratio = 1.2 if prefix in ('30', '68') else 1.1
        res = get_data2(code)
        date_list = res['date']
        CLOSE = res['close']
        if len(CLOSE) < N + 2:
            continue

        for j in range(N + 2, len(CLOSE)):
            ma55 = MA(CLOSE[j - 55: j], N)[-1]
            ma55_b = MA(REF(CLOSE, 2)[j - 55: j], N)[-1]
            count = 0
            if CLOSE[j] > ma55 > ma55_b:
                for k in range(7):
                    if is_zt(CLOSE[j - k - 1], CLOSE[j - k], ratio):
                        count += 1
                        if count == 2:
                            item = [date_list[j], code]
                            ret.append(item)
                            break

    df = pd.DataFrame(ret, columns=['DATE_T', 'STK_CODE'])
    return df


def is_zt(c1, c2, ratio):
    flag = abs(c2 - c1 * ratio) < 0.01
    return flag


def bias(c, ma):
    value = (c / ma - 1) * 100
    return value


def calc():
    # dr_df = get_double_ring()
    # stocks = dr_df['STK_CODE'].unique()
    sql = "SELECT DISTINCT code FROM STOCK_DAY_DATA"
    r = exec_sql(sql)
    stocks = [tok[0] for tok in r]
    # stocks = ['688480']
    M, N1, N2, N3, N4, N5, M1, M2, P = 26, 12, 144, 169, 576, 676, 60, 160, 3
    ret = list()

    for i, code in enumerate(stocks):
        print(f'len={len(stocks)}->i={i}->code={code}')
        res = get_data(code)
        date_list = res['date']
        CLOSE = res['close']
        OPEN = res['open']
        LOW = res['low']
        HIGH = res['high']
        if len(CLOSE) - N4 <= 0:
            print(f'{code} not fit')
            continue
        # P1 = EMA(CLOSE, N1)
        P2 = EMA(CLOSE, N2)
        P3 = EMA(CLOSE, N3)
        P4 = EMA(CLOSE, N4)
        MA20 = MA(CLOSE, 20)
        BIAS = bias(CLOSE, MA20)
        P5 = EMA(CLOSE, N5)
        J0 = (HIGH + LOW + CLOSE + OPEN) / 4
        L1 = LLV(J0, M1)
        # H2 = HHV(J0, M2)
        # ATR1 = ATR(CLOSE)
        # PJ = EMA(CLOSE, M)
        # UP1 = EMA(PJ + (K * ATR1), P)
        # DN = EMA(PJ - (K * ATR1), P)
        for j in range(N4, len(CLOSE)):
            MAX_A = max(P2[j], P3[j])
            MIN_A = min(P2[j], P3[j])
            MAX_B = max(P4[j], P5[j])
            # print(f'code={code}->dt={date_list[j]}->p2={P2[j]}->p3={P3[j]}->MAX_A={MAX_A}->MIN_A={MIN_A}->p4={P4[j]}'
            #       f'->p5={P5[j]}->MAX_B={MAX_B}->L1={L1[j]}->L1_M1={L1[j - M1]}->bias={BIAS[j]}')
            if MIN_A > MAX_B and CLOSE[j] > MAX_A and CLOSE[j] > MAX_B and L1[j] > L1[j - M1] and BIAS[j] < 6:
                item = [date_list[j], code]
                ret.append(item)
        # print(df)
        # print(20*'-')
        if i == 20:
            break
    df = pd.DataFrame(ret, columns=['DATE_T', 'STK_CODE'])
    df = df.loc[df['DATE_T'] >= 20211210]
    # data = pd.merge(dr_df, df, on=['DATE_T', 'STK_CODE'])
    # data.to_excel(r'C:\Users\AndrewX\Desktop\超级趋势股票.xlsx', index=False)
    df.to_excel(r'C:\Users\AndrewX\Desktop\超级趋势股票.xlsx', index=False)
    insert_db(df, 'CJQS_STOCKS')
    # print(df)


def get_data(code):
    sql = f"SELECT code, datetime, [close], [open], low, high FROM STOCK_DAY_DATA WHERE code={code}"
    r = exec_sql(sql)
    date_ = [tok[1] for tok in r]
    close = np.array([tok[2] for tok in r])
    open_ = np.array([tok[3] for tok in r])
    low = np.array([tok[4] for tok in r])
    high = np.array([tok[5] for tok in r])
    res = {'date': date_, 'close': close, 'open': open_, 'low': low, 'high': high}

    return res


def get_data2(code):
    sql = f"SELECT code, datetime, [close] FROM STOCK_DAY_DATA WHERE code={code}"
    r = exec_sql(sql)
    date_ = [tok[1] for tok in r]
    close = np.array([tok[2] for tok in r])
    res = {'date': date_, 'close': close}

    return res


def exec_sql(sql):
    conn = pymssql.connect(host=host, port=port, user=user, password=password, database=db)
    cursor = conn.cursor()
    cursor.execute(sql)
    r = cursor.fetchall()
    cursor.close()
    conn.close()
    return r


def insert_db(df, table_name):
    yconnect = create_engine(f'mssql+pymssql://{user}:{urlquote(password)}@{host}:{port}/{db}?charset=utf8')
    pd.io.sql.to_sql(df, table_name, yconnect, schema=schema, if_exists='append', index=False)


def factor1(close, inclose, M):
    A1 = EMA(close, 2)
    B1 = EMA(inclose, 2)

    BD1 = MA((A1 / REF(A1, M) - 1) * 100, 3)
    BD2 = MA((B1 / REF(B1, M) - 1) * 100, 3)
    DN1 = BD1 - BD2
    flag = DN1 > 10

    return flag[-120:]


def factor2(high, low, N1, N2):
    A1 = (high - LLV(high, N1)) / (HHV(high, N1) - LLV(high, N1))
    A2 = (HHV(low, N1) - low) / (HHV(low, N2) - LLV(low, N2))
    pre_high, pre_low = REF(high, 1), REF(low, 1)
    HHH, LLL = np.zeros_like(high), np.zeros_like(high)

    for i in range(len(high)):
        HHH[i] = A1[i] if high[i] > pre_high[i] else 0
        LLL[i] = A2[i] if low[i] < pre_low[i] else 0

    B1 = EMA(HHH, N2) * 100
    S1 = EMA(LLL, N2) * 100
    DN2 = B1 - S1
    flag = DN2 > 8

    return flag[-120:]


def QSLD(close, high, low):
    N1, M1 = 120, 73

    H1 = HHV(high, N1)
    L1 = LLV(low, M1)
    zz = (H1 + L1) / 2
    flag = close > zz

    return flag[-120:]


if __name__ == '__main__':
    calc()
